"""empty message

Revision ID: c058421705ec
Revises: 6c669d9ec3bd
Create Date: 2023-04-20 16:28:00.255353

"""
from datetime import datetime, timezone
from alembic import op
import sqlalchemy as sa
from sqlalchemy import orm, inspect
from os import listdir
from os.path import isfile, join

from app.config import UPLOAD_FOLDER, db

DeclarativeBase = orm.declarative_base()

# revision identifiers, used by Alembic.
revision = 'c058421705ec'
down_revision = '6c669d9ec3bd'
branch_labels = None
depends_on = None

class File(DeclarativeBase):
    __tablename__ = 'file'
    filename = sa.Column(sa.String, primary_key=True)
    created_at = sa.Column(sa.DateTime, nullable=False, default=datetime.now(timezone.utc))
    updated_at = sa.Column(sa.DateTime, nullable=False, default=datetime.now(timezone.utc))
    created_by = sa.Column(sa.Integer, sa.ForeignKey('user.id'), nullable=True)

class Recipe(DeclarativeBase):
    __tablename__ = 'recipe'
    id = sa.Column(sa.Integer, primary_key=True)
    photo = sa.Column(sa.String())

class Household(DeclarativeBase):
    __tablename__ = 'household'
    id = sa.Column(sa.Integer, primary_key=True)
    photo = sa.Column(sa.String())

class User(DeclarativeBase):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    admin = sa.Column(sa.Boolean(), default=False)
    photo = sa.Column(sa.String())

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    inspector = inspect(db.engine)
    if not inspector.has_table("file"):
        op.create_table('file',
        sa.Column('filename', sa.String(), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.Column('created_by', sa.Integer(), nullable=True),
        sa.ForeignKeyConstraint(['created_by'], ['user.id'], name=op.f('fk_file_created_by_user')),
        sa.PrimaryKeyConstraint('filename', name=op.f('pk_file')),
        )
    # ### end Alembic commands ###
    bind = op.get_bind()
    session = orm.Session(bind=bind)


    try:
        filesInUploadFolder = [f for f in listdir(UPLOAD_FOLDER) if isfile(join(UPLOAD_FOLDER, f))]
        files = [File(filename=f) for f in filesInUploadFolder if not session.query(File.filename).filter(File.filename == f).first()]

        session.bulk_save_objects(files)
        session.commit()
    except FileNotFoundError as e:
        session.rollback()
    except BaseException as e:
        session.rollback()
        raise e
    
    with op.batch_alter_table('household', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_household_photo_file'), 'file', ['photo'], ['filename'])

    with op.batch_alter_table('recipe', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_recipe_photo_file'), 'file', ['photo'], ['filename'])

    with op.batch_alter_table('expense', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_expense_photo_file'), 'file', ['photo'], ['filename'])

    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.create_foreign_key(batch_op.f('fk_user_photo_file'), 'file', ['photo'], ['filename'])


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('recipe', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_recipe_photo_file'), type_='foreignkey')

    with op.batch_alter_table('household', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_household_photo_file'), type_='foreignkey')

    with op.batch_alter_table('expense', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_expense_photo_file'), type_='foreignkey')

    with op.batch_alter_table('user', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_user_photo_file'), type_='foreignkey')
    
    op.drop_table('file')
    # ### end Alembic commands ###
